CREATE OR REPLACE FUNCTION tzq.get_table_by_task_id(task_id int8)
  RETURNS TABLE(table_name varchar, column_name varchar, s1 varchar, s2 varchar, s3 varchar, s4 varchar, s5 varchar, s6 varchar, s7 varchar) AS $BODY$
DECLARE
  subquery varchar;
begin
  subquery := '
with tn as (
  select ' || task_id || ' as task_id
)
,s as (
-- {plan_dev_db,plan_icsl_db,plan_prac1_db,plan_prac2_db,plan_press_db,plan_sit_db,plan_uat_db}
select array_agg(distinct source) as arr,array_length(array_agg(distinct source), 1) as len
  from compare_task_info_v where task_id = (select task_id from tn)
)
,tab as (
select distinct table_name,column_name
  from compare_task_info_v where task_id = (select task_id from tn)
 order by table_name,column_name
)
,e1 as (
select table_name,column_name,source as s1
  from compare_task_info_v 
 where task_id = (select task_id from tn)
   and source = (select arr[1] from s)
)
,e2 as (
select table_name,column_name,source as s2
  from compare_task_info_v 
 where task_id = (select task_id from tn)
   and source = (select arr[2] from s)
)
,e3 as (
select table_name,column_name,source as s3
  from compare_task_info_v 
 where task_id = (select task_id from tn)
   and source = (select arr[3] from s)
)
,e4 as (
select table_name,column_name,source as s4
  from compare_task_info_v 
 where task_id = (select task_id from tn)
   and source = (select arr[4] from s)
)
,e5 as (
select table_name,column_name,source as s5
  from compare_task_info_v 
 where task_id = (select task_id from tn)
   and source = (select arr[5] from s)
)
,e6 as (
select table_name,column_name,source as s6
  from compare_task_info_v 
 where task_id = (select task_id from tn)
   and source = (select arr[6] from s)
)
,e7 as (
select table_name,column_name,source as s7
  from compare_task_info_v 
 where task_id = (select task_id from tn)
   and source = (select arr[7] from s)
)
select tab.*
      ,case when e1.s1 is not null then ''√'' else ''×''::varchar end as s1
      ,case when e2.s2 is not null then ''√'' else ''×''::varchar end as s2
      ,case when e3.s3 is not null then ''√'' else ''×''::varchar end as s3
      ,case when e4.s4 is not null then ''√'' else ''×''::varchar end as s4
      ,case when e5.s5 is not null then ''√'' else ''×''::varchar end as s5
      ,case when e6.s6 is not null then ''√'' else ''×''::varchar end as s6
      ,case when e7.s7 is not null then ''√'' else ''×''::varchar end as s7
  from tab
  left join e1
    on tab.table_name  = e1.table_name 
   and tab.column_name = e1.column_name 
  left join e2
    on tab.table_name  = e2.table_name 
   and tab.column_name = e2.column_name 
  left join e3
    on tab.table_name  = e3.table_name 
   and tab.column_name = e3.column_name 
  left join e4
    on tab.table_name  = e4.table_name 
   and tab.column_name = e4.column_name 
  left join e5
    on tab.table_name  = e5.table_name 
   and tab.column_name = e5.column_name 
  left join e6
    on tab.table_name  = e6.table_name 
   and tab.column_name = e6.column_name 
  left join e7
    on tab.table_name  = e7.table_name 
   and tab.column_name = e7.column_name 
';
  RETURN QUERY execute subquery;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 100000;

ALTER FUNCTION tzq.get_table_by_task_id(task_id int8) OWNER TO tzq;